DECLARE
  v_user_name VARCHAR2(100);
  v_dir_views       VARCHAR2(100) := '_VIEWS_DIR';
  v_dir_types       VARCHAR2(100) := '_TYPES_DIR';
  v_dir_packages    VARCHAR2(100) := '_PACKAGES_DIR';
  v_dir_package_bod VARCHAR2(100) := '_PACKAGE_BOD_DIR';
  v_dir_package_grants VARCHAR2(100) := '_PACKAGE_GRANTS_DIR';
  v_dir_functions   VARCHAR2(100) := '_FUNCTIONS_DIR';
  v_dir_procedures  VARCHAR2(100) := '_PROCEDURES_DIR';
  v_dir_mviews      VARCHAR2(100) := '_MVIEWS_DIR';
  v_dir_triggers    VARCHAR2(100) := '_TRIGGERS_DIR';
  
  FUNCTION trim_chr10(p_str VARCHAR2) RETURN VARCHAR2 IS
    v_cnt NUMBER;
    var   VARCHAR2(4000);
  BEGIN
    var := p_str;
    dbms_output.put_line(var); 
    v_cnt := LENGTH(REPLACE(var, CHR(10)));
    FOR i IN 1..v_cnt LOOP
      var := REPLACE(var,CHR(10)||CHR(10),CHR(10));
    END LOOP;

    var := SUBSTR(var,1,length(var) /*- 1*/);
    RETURN var;
  END;
  
  PROCEDURE output_clob (
    p_str    CLOB,
    p_handle utl_file.file_type,
    p_size   NUMBER
  )
  IS
    pos        INTEGER;
    pos_space  INTEGER;
    ret_size   INTEGER;
    tmp_str    VARCHAR2 (4000);
    v_add      INTEGER          := 1;
    sub_str    VARCHAR2 (4000);
    trim_str   VARCHAR2 (4000);
  BEGIN    
    pos := 1;
    ret_size := LENGTH (p_str);
    dbms_output.put_line(ret_size);

    LOOP
      IF (ret_size >= p_size) THEN
        
        tmp_str := substr (p_str, pos, p_size);

        pos_space := INSTR (tmp_str, CHR (10), -1) - 1;

        IF pos_space = 0 THEN
          pos_space := INSTR (tmp_str, ' ', -1) - 1;
        END IF;

        IF pos_space = 0 THEN
          pos_space := p_size;
          v_add := 0;
        END IF;

        sub_str := SUBSTR (p_str, pos, pos_space);
        --trim_str := trim_chr10(sub_str);
        utl_file.put_line(p_handle, CONVERT(sub_str, 'CL8MSWIN1251'));
      ELSE
        sub_str := SUBSTR (p_str, pos, ret_size);
        --trim_str := trim_chr10(sub_str);
        utl_file.put_line(p_handle, CONVERT(sub_str, 'CL8MSWIN1251'));

        --utl_file.put_line(p_handle, SUBSTR (p_str, pos, ret_size));
        EXIT;
      END IF;

      ret_size := ret_size - pos_space - v_add;
      pos := pos + pos_space + v_add;
    END LOOP;
  END output_clob;
  
  PROCEDURE p_write_type_src(p_dir_name VARCHAR2,
                             p_user     VARCHAR
                             ) IS
    v_handle utl_file.file_type;
    v_cnt    NUMBER := 0;
    v_clob   CLOB;
    ii       NUMBER := 1;
    CURSOR type_hierarchy_drop IS
    SELECT * FROM (
          SELECT sel.NAME,
                 lvl,
                 row_number() over(PARTITION BY sel.NAME ORDER BY lvl DESC) AS rn
            FROM (SELECT LEVEL AS lvl,
                         t.NAME,
                         t.referenced_name
                    FROM dba_dependencies t
                   WHERE t.TYPE = 'TYPE'
                     AND t.referenced_type = 'TYPE'
                     AND t.owner = p_user
                     --AND t.referenced_owner = p_user
                  
                  CONNECT BY NOCYCLE t.referenced_name = PRIOR t.NAME
                   START WITH t.NAME IN (
                                         SELECT t.object_name --,
                                         -- dep.referenced_name
                                           FROM dba_objects      t,
                                                dba_dependencies dep --,
                                         --user_objects      tb
                                          WHERE t.object_type = 'TYPE'
                                            AND dep.TYPE(+) = 'TYPE'
                                            AND dep.NAME(+) = t.object_name
                                            AND dep.referenced_type(+) = 'TYPE'
                                            AND dep.NAME IS NULL
                                            AND t.owner = p_user
                                            AND dep.owner(+) = p_user
                                            AND dep.referenced_owner(+) = p_user
                                         --AND t.object_name = tb.object_name(+)
                                         --AND tb.object_type(+) = 'TYPE BODY'
                                         --AND tb.object_name IS NULL
                                         )
                  UNION ALL
                  
                  SELECT 1             AS lvl,
                         t.object_name AS NAME,
                         NULL          AS referenced_name
                  -- dep.referenced_name
                    FROM dba_objects      t,
                         dba_dependencies dep --,
                  --user_objects      tb
                   WHERE t.object_type = 'TYPE'
                     AND dep.TYPE(+) = 'TYPE'
                     AND dep.NAME(+) = t.object_name
                     AND dep.referenced_type(+) = 'TYPE'
                     AND dep.NAME IS NULL
                     AND t.owner = p_user
                     AND dep.owner(+) = p_user
                     AND dep.referenced_owner(+) = p_user
                  --AND t.object_name = tb.object_name(+)
                  --AND tb.object_type(+) = 'TYPE BODY'
                  --AND tb.object_name IS NULL
                  ) sel) sel2
           WHERE rn = 1
           ORDER BY lvl DESC,
                    NAME,
                    rn
    ;
    
    CURSOR type_hierarchy_create IS
    SELECT * FROM (
          SELECT sel.NAME,
                 lvl,
                 row_number() over(PARTITION BY sel.NAME ORDER BY lvl DESC) AS rn
            FROM (SELECT LEVEL AS lvl,
                         t.NAME,
                         t.referenced_name
                    FROM dba_dependencies t
                   WHERE t.TYPE = 'TYPE'
                     AND t.referenced_type = 'TYPE'
                     AND t.owner = p_user
                     --AND t.referenced_owner = p_user
                  
                  CONNECT BY NOCYCLE t.referenced_name = PRIOR t.NAME
                   START WITH t.NAME IN (
                                         SELECT t.object_name --,
                                         -- dep.referenced_name
                                           FROM dba_objects      t,
                                                dba_dependencies dep --,
                                         --user_objects      tb
                                          WHERE t.object_type = 'TYPE'
                                            AND dep.TYPE(+) = 'TYPE'
                                            AND dep.NAME(+) = t.object_name
                                            AND dep.referenced_type(+) = 'TYPE'
                                            AND dep.NAME IS NULL
                                            AND t.owner = p_user
                                            AND dep.owner(+) = p_user
                                            AND dep.referenced_owner(+) = p_user
                                         --AND t.object_name = tb.object_name(+)
                                         --AND tb.object_type(+) = 'TYPE BODY'
                                         --AND tb.object_name IS NULL
                                         )
                  UNION ALL
                  
                  SELECT 1             AS lvl,
                         t.object_name AS NAME,
                         NULL          AS referenced_name
                  -- dep.referenced_name
                    FROM dba_objects      t,
                         dba_dependencies dep --,
                  --user_objects      tb
                   WHERE t.object_type = 'TYPE'
                     AND dep.TYPE(+) = 'TYPE'
                     AND dep.NAME(+) = t.object_name
                     AND dep.referenced_type(+) = 'TYPE'
                     AND dep.NAME IS NULL
                     AND t.owner = p_user
                     AND dep.owner(+) = p_user
                     AND dep.referenced_owner(+) = p_user
                  --AND t.object_name = tb.object_name(+)
                  --AND tb.object_type(+) = 'TYPE BODY'
                  --AND tb.object_name IS NULL
                  ) sel) sel2
           WHERE rn = 1
           ORDER BY lvl,
                    NAME,
                    rn
    ;
    
  BEGIN

    ii := 0;

    FOR i IN type_hierarchy_drop LOOP
      IF ii = 0 THEN
        v_handle := utl_file.fopen(location => p_dir_name, filename => 'TYPES.sql', open_mode => 'W', max_linesize => 4000);
        --utl_file.put_line(v_handle, 'SPOOL err.txt');
      END IF;
      ii := ii + 1;
      utl_file.put_line(v_handle, 'DROP TYPE ' || i.name || chr(10) || '/');
    END LOOP;
    
    FOR i IN type_hierarchy_create LOOP

      utl_file.put_line(v_handle, 'CREATE OR REPLACE ');
      FOR j IN (SELECT * FROM dba_source t WHERE t.type = 'TYPE' AND t.name = i.name AND t.owner = p_user ORDER BY line) LOOP
        utl_file.put_line(v_handle, j.text);
      END LOOP;
      --utl_file.put_line(v_handle, chr(10) || '/');
      --v_clob := dbms_metadata.get_ddl(object_type => 'TYPE', name => i.name, schema => p_user);
      --output_clob(v_clob, v_handle, 4000);
      utl_file.put_line(file => v_handle, buffer => chr(10) || '/');
      
      SELECT COUNT(1)
        INTO v_cnt
        FROM dba_source t
       WHERE t.type = 'TYPE BODY' AND t.name = i.name AND t.owner = p_user;
      IF v_cnt > 0 THEN
        utl_file.put_line(v_handle, 'CREATE OR REPLACE ');
        FOR j IN (SELECT * FROM dba_source t WHERE t.type = 'TYPE BODY' AND t.name = i.name AND t.owner = p_user ORDER BY line) LOOP
          utl_file.put_line(v_handle, CONVERT(j.text,'CL8MSWIN1251'));
        END LOOP;
        utl_file.put_line(file => v_handle, buffer => chr(10) || '/');
      END IF;
      
      FOR pr IN (SELECT * FROM dba_tab_privs t
                  WHERE t.owner = p_user
                    AND t.table_name = i.name) LOOP
        --v_clob := dbms_metadata.get_dependent_ddl(object_type => 'OBJECT_GRANT', base_object_name => i.view_name);
        utl_file.put_line(file => v_handle, buffer => 'GRANT ' || pr.privilege || ' ON ' || pr.table_name || ' TO ' || pr.grantee ||
                               CASE WHEN pr.grantable = 'YES' THEN ' WITH GRANT OPTION' END || ';');
      END LOOP;

    END LOOP;
    
    IF utl_file.is_open(v_handle) THEN
      utl_file.fclose(file => v_handle);
    END IF;
    
  END p_write_type_src;
  
  PROCEDURE p_write_grants_src(p_object_type         VARCHAR2,
                               p_dir_name            VARCHAR2,
                               p_user                VARCHAR2) IS
    v_handle      utl_file.file_type;
    v_cnt NUMBER := 0;
    v_buffer CLOB;
  BEGIN

    FOR i IN (SELECT DISTINCT t.object_name FROM dba_objects t WHERE t.object_type = upper(p_object_type) AND t.owner = p_user ) LOOP
      
      SELECT COUNT(1)
        INTO v_cnt
        FROM dba_tab_privs t
       WHERE t.owner = p_user
         AND t.table_name = i.object_name;

      IF v_cnt > 0 THEN
        v_buffer := NULL;
        v_handle := utl_file.fopen(location => p_dir_name, filename => REPLACE (REPLACE (i.object_name, '/', '_'), '$', '_')  || '.sql', open_mode => 'W', max_linesize => 4000);
        -- grants --
        FOR pr IN (SELECT * FROM dba_tab_privs t
                    WHERE t.owner = p_user
                      AND t.table_name = i.object_name) LOOP
          --v_clob := dbms_metadata.get_dependent_ddl(object_type => 'OBJECT_GRANT', base_object_name => i.view_name);
          utl_file.put_line(file => v_handle, buffer => 'GRANT ' || pr.privilege || ' ON ' || pr.table_name || ' TO ' || pr.grantee ||
                                 CASE WHEN pr.grantable = 'YES' THEN ' WITH GRANT OPTION' END || ';');

        END LOOP;

        IF utl_file.is_open(v_handle) THEN
          utl_file.fclose(file => v_handle);
        END IF;
      END IF;

    END LOOP;

  END p_write_grants_src;
  
  PROCEDURE p_write_src(p_object_type         VARCHAR2,
                        p_dir_name            VARCHAR2,
                        p_user                VARCHAR2,
                        p_exclude_view_trig   BOOLEAN DEFAULT FALSE,
                        p_exclude_mview_trig  BOOLEAN DEFAULT FALSE) IS
    v_handle      utl_file.file_type;
    --v_handle_all  utl_file.file_type;
    v_cnt NUMBER := 0;
    v_buffer CLOB;
  BEGIN

    --v_handle_all := utl_file.fopen(location => p_dir_name, filename => replace(p_object_type, ' ', '_') || '.sql',
    --                               open_mode => 'W', max_linesize => 4000);
    --utl_file.put_line(file => v_handle_all, buffer => 'SPOOL err.txt');

    FOR i IN (SELECT DISTINCT t.object_name FROM dba_objects t WHERE t.object_type = upper(p_object_type) AND t.owner = p_user ) LOOP
      
      IF p_exclude_view_trig THEN
        SELECT COUNT(1)
          INTO v_cnt
          FROM dba_triggers t,
               dba_views    v,
               dba_source   ss
         WHERE t.table_name = v.view_name
           AND t.trigger_name = ss.name
           AND t.trigger_name = i.object_name
           AND t.owner = p_user
           AND v.owner = p_user
           AND ss.owner = p_user
           AND ROWNUM = 1;
      ELSIF p_exclude_mview_trig THEN
        SELECT COUNT(1)
          INTO v_cnt
          FROM dba_triggers  t,
               dba_mviews    v,
               dba_source    ss
         WHERE t.table_name = v.mview_name
           AND t.trigger_name = ss.name
           AND t.trigger_name = i.object_name
           AND t.owner = p_user
           AND v.owner = p_user
           AND ss.owner = p_user
           AND ROWNUM = 1;
      ELSE
        v_cnt := 0;
      END IF;
      
      IF v_cnt = 0 THEN
        --utl_file.put_line(file => v_handle_all, buffer => '@@' || lower(REPLACE (i.object_name, '/', '_')) || '.sql');
        
        v_buffer := NULL;
        v_handle := utl_file.fopen(location => p_dir_name, filename => REPLACE (REPLACE (i.object_name, '/', '_'), '$', '_')  || '.sql', open_mode => 'W', max_linesize => 4000);
        utl_file.put_line(file => v_handle, buffer => 'CREATE OR REPLACE ', autoflush => TRUE);
        --v_buffer := dbms_metadata.get_ddl(object_type => p_object_type , name => i.object_name, schema => p_user);
        FOR j IN (SELECT * FROM dba_source t WHERE t.name = i.object_name AND t.type = p_object_type AND t.owner = p_user ORDER BY line) LOOP
          v_buffer := v_buffer || j.text;
        END LOOP;
        output_clob(v_buffer, v_handle, 4000);
        utl_file.put_line(file => v_handle, buffer => chr(10) || '/');
        
        -- grants --
        IF p_object_type IN ('FUNCTION', 'PROCEDURE') THEN
          FOR pr IN (SELECT * FROM dba_tab_privs t
                      WHERE t.owner = p_user
                        AND t.table_name = i.object_name) LOOP
            --v_clob := dbms_metadata.get_dependent_ddl(object_type => 'OBJECT_GRANT', base_object_name => i.view_name);
            utl_file.put_line(file => v_handle, buffer => 'GRANT ' || pr.privilege || ' ON ' || pr.table_name || ' TO ' || pr.grantee ||
                                   CASE WHEN pr.grantable = 'YES' THEN ' WITH GRANT OPTION' END || ';');

          END LOOP;
        END IF;

        IF utl_file.is_open(v_handle) THEN
          utl_file.fclose(file => v_handle);
        END IF;
      END IF;

    END LOOP;

    --IF utl_file.is_open(v_handle_all) THEN
    --  utl_file.put_line(file => v_handle_all, buffer => 'exit');
    --  utl_file.fclose(file => v_handle_all);
    --END IF;

  END p_write_src;
  
  PROCEDURE p_write_views_src(p_dir_name VARCHAR2, p_user VARCHAR2) IS
    v_handle   utl_file.file_type;
    --v_handle_v utl_file.file_type;
    v_cnt      NUMBER;
    v_clob     CLOB;
  BEGIN

    --v_handle_v := utl_file.fopen(p_dir_name, 'views.sql', 'W', 4000);
    --utl_file.put_line(file => v_handle_v, buffer => 'SPOOL err.txt');
    
    FOR i IN (SELECT DISTINCT t.view_name FROM dba_views t WHERE t.owner = p_user ) LOOP
                
      --utl_file.put_line(file => v_handle_v, buffer => '@@' || i.view_name || '.sql');
     
      v_handle := utl_file.fopen(p_dir_name, REPLACE (i.view_name, '$', '_') || '.sql', 'W', 4000);
      v_clob := dbms_metadata.get_ddl(object_type => 'VIEW', name => i.view_name, schema => p_user);
      output_clob(v_clob, v_handle, 4000);
      
      utl_file.put_line(file => v_handle, buffer => '/');
      
      -- trigger src --
      v_cnt := 0;
      FOR j IN (SELECT ss.text
                  FROM dba_triggers t,
                       dba_views    v,
                       dba_source   ss
                 WHERE t.table_name = v.view_name
                   AND t.trigger_name = ss.name
                   AND v.view_name = i.view_name
                   AND t.owner = p_user
                   AND v.owner = p_user
                   AND ss.owner = p_user
                 ORDER BY ss.line
                 ) LOOP
         IF v_cnt = 0 THEN
           utl_file.put_line(file => v_handle, buffer => 'CREATE OR REPLACE ', autoflush => TRUE);
         END IF;
         
         utl_file.put_line(file => v_handle, buffer => CONVERT(j.text,'CL8MSWIN1251'), autoflush => TRUE);
         v_cnt := v_cnt + 1;
      END LOOP;

      IF v_cnt > 0 THEN
        utl_file.put_line(file => v_handle, buffer => '/');
      END IF;
      
      FOR pr IN (SELECT * FROM dba_tab_privs t
                  WHERE t.owner = p_user
                    AND t.table_name = i.view_name) LOOP
        --v_clob := dbms_metadata.get_dependent_ddl(object_type => 'OBJECT_GRANT', base_object_name => i.view_name);
        utl_file.put_line(file => v_handle, buffer => 'GRANT ' || pr.privilege || ' ON ' || pr.table_name || ' TO ' || pr.grantee ||
                               CASE WHEN pr.grantable = 'YES' THEN ' WITH GRANT OPTION' END || ';');
      END LOOP;
      
      IF utl_file.is_open(v_handle) THEN
        utl_file.fclose(file => v_handle);
      END IF;

    END LOOP;

    --IF utl_file.is_open(v_handle_v) THEN
    --  utl_file.put_line(file => v_handle_v, buffer => 'exit' || chr(10));
    --  utl_file.fclose(file => v_handle_v);
    --END IF;

  END p_write_views_src;
  
  PROCEDURE p_write_mviews_src(p_dir_name VARCHAR2, p_user VARCHAR2) IS
    v_handle     utl_file.file_type;
    --v_handle_all utl_file.file_type;
    v_cnt        NUMBER;
    v_clob       CLOB;
  BEGIN

    --v_handle_all := utl_file.fopen(p_dir_name, 'mviews.sql', 'W', 4000);
    --utl_file.put_line(file => v_handle_all, buffer => 'SPOOL err.txt');
    
    FOR i IN (SELECT t.* FROM dba_mviews t WHERE t.owner = p_user) LOOP
                
      --utl_file.put_line(file => v_handle_all, buffer => '@@' || i.mview_name || '.sql');
      
      v_handle := utl_file.fopen(p_dir_name, REPLACE (i.mview_name, '$', '_') || '.sql', 'W', 4000);
      
      utl_file.put_line(file => v_handle, buffer => 'DROP MATERIALIZED VIEW ' || i.mview_name || chr(10) || '/');
         
      -- get mat view ddl --
      v_clob := dbms_metadata.get_ddl(object_type => 'MATERIALIZED_VIEW', name => i.mview_name, schema => p_user);      
      output_clob(v_clob, v_handle, 255);
      utl_file.put_line(file => v_handle, buffer => '/');

      -- get dependent ddl --
      FOR pr IN (SELECT * FROM dba_tab_privs t
                  WHERE t.owner = p_user
                    AND t.table_name = i.mview_name) LOOP
        --v_clob := dbms_metadata.get_dependent_ddl(object_type => 'OBJECT_GRANT', base_object_name => i.view_name);
        utl_file.put_line(file => v_handle, buffer => 'GRANT ' || pr.privilege || ' ON ' || pr.table_name || ' TO ' || pr.grantee ||
                                  CASE WHEN pr.grantable = 'YES' THEN ' WITH GRANT OPTION' END || ';');
      END LOOP;
      
      utl_file.put_line(file => v_handle, buffer => '/');
      
      -- trigger src --
      v_cnt := 0;
      FOR j IN (SELECT ss.text
                  FROM dba_triggers t,
                       dba_mviews    v,
                       dba_source   ss
                 WHERE t.table_name = v.mview_name
                   AND t.trigger_name = ss.name
                   AND v.mview_name = i.mview_name
                   AND t.owner = p_user
                   AND v.owner = p_user
                   AND ss.owner = p_user
                 ORDER BY ss.line
                 ) LOOP
         IF v_cnt = 0 THEN
           utl_file.put_line(file => v_handle, buffer => 'CREATE OR REPLACE ', autoflush => TRUE);
         END IF;
         utl_file.put(file => v_handle, buffer => j.text);
         v_cnt := v_cnt + 1;
      END LOOP;

      IF v_cnt > 0 THEN
        utl_file.put_line(file => v_handle, buffer => chr(10) || '/');
      END IF;
      
      SELECT COUNT(1)
        INTO v_cnt
        FROM dba_indexes t
       WHERE t.table_name = i.mview_name
         AND t.table_owner = p_user
         AND t.index_name NOT LIKE 'I_SNAP$%';
      IF v_cnt > 0 THEN
        v_clob := dbms_metadata.get_dependent_ddl(object_type => 'INDEX',
                                                  base_object_name => i.mview_name,
                                                  base_object_schema => p_user);
        output_clob(v_clob, v_handle, 4000);
        utl_file.put_line(file => v_handle, buffer => chr(10) || '/');
      END IF;
      
      IF utl_file.is_open(v_handle) THEN
        utl_file.fclose(file => v_handle);
      END IF;
    
    END LOOP;
    
    --IF utl_file.is_open(v_handle_all) THEN
    --  utl_file.put_line(file => v_handle_all, buffer => 'exit' || chr(10));
    --  utl_file.fclose(file => v_handle_all);
    --END IF;
  END p_write_mviews_src;

BEGIN 
  v_user_name := upper('&scheme');
  v_dir_views       := v_user_name || '_VIEWS_DIR';
  v_dir_types       := v_user_name || '_TYPES_DIR';
  v_dir_packages    := v_user_name || '_PACKAGES_DIR';
  v_dir_package_bod := v_user_name || '_PACKAGE_BOD_DIR';
  v_dir_package_grants := v_user_name || '_PACKAGE_GRANTS_DIR';
  v_dir_functions   := v_user_name || '_FUNCTIONS_DIR';
  v_dir_procedures  := v_user_name || '_PROCEDURES_DIR';
  v_dir_mviews      := v_user_name || '_MVIEWS_DIR';
  v_dir_triggers    := v_user_name || '_TRIGGERS_DIR';

  p_write_src('PACKAGE', v_dir_packages, v_user_name);
  p_write_src('PROCEDURE', v_dir_procedures, v_user_name);
  p_write_src('FUNCTION', v_dir_functions, v_user_name);
  p_write_src('TRIGGER', v_dir_triggers, v_user_name, TRUE);
  p_write_src('PACKAGE BODY', v_dir_package_bod, v_user_name);
  p_write_grants_src('PACKAGE',v_dir_package_grants,v_user_name);
  p_write_views_src(v_dir_views, v_user_name);
  p_write_mviews_src(v_dir_mviews, v_user_name);
  p_write_type_src(v_dir_types, v_user_name);

END;
/
